# src/db/transaction_db.py
from src.db.connection import get_db_connection, close_db_connection
from src.utils.logger import get_logger

logger = get_logger(__name__)

def create_transaction(
    buyer_id: int,
    seller_id: int,
    artwork_id: int,
    amount: float,
    status: str = "待付款"  # 状态：待付款/待收货/交易完成/已取消
) -> str:
    """创建新交易（返回交易ID，失败返回None）"""
    conn = get_db_connection()
    if not conn:
        return None
    
    try:
        with conn.cursor() as cursor:
            # 1. 生成交易ID（格式：TRA+年份+6位序号，如TRA2024000001）
            current_year = str(pd.Timestamp.now().year)  # 需导入pandas，或用datetime
            cursor.execute("""
                SELECT MAX(RIGHT(transaction_id, 6)) AS max_seq 
                FROM transaction 
                WHERE LEFT(transaction_id, 6) = CONCAT('TRA', %s)
            """, (current_year,))
            max_seq = cursor.fetchone()["max_seq"] or "000000"
            new_seq = f"{int(max_seq) + 1:06d}"  # 补零为6位
            transaction_id = f"TRA{current_year}{new_seq}"
            
            # 2. 插入交易记录
            sql = """
            INSERT INTO transaction 
            (transaction_id, buyer_id, seller_id, artwork_id, amount, status, create_time)
            VALUES (%s, %s, %s, %s, %s, %s, CURRENT_TIMESTAMP)
            """
            cursor.execute(
                sql,
                (transaction_id, buyer_id, seller_id, artwork_id, amount, status)
            )
            conn.commit()
            logger.info(f"交易创建成功（transaction_id: {transaction_id}）")
            return transaction_id
    except Exception as e:
        conn.rollback()
        logger.error(f"创建交易失败（buyer_id: {buyer_id}, artwork_id: {artwork_id}）：{str(e)}")
        return None
    finally:
        close_db_connection(conn)

def get_transactions_by_buyer(buyer_id: int, status: str = None) -> list:
    """获取买家的交易列表（支持按状态筛选）"""
    conn = get_db_connection()
    if not conn:
        return []
    
    try:
        with conn.cursor() as cursor:
            sql = "SELECT * FROM transaction WHERE buyer_id = %s"
            params = [buyer_id]
            
            # 按状态筛选
            if status:
                sql += " AND status = %s"
                params.append(status)
            
            sql += " ORDER BY create_time DESC"
            cursor.execute(sql, params)
            transactions = cursor.fetchall()
            logger.info(f"获取买家交易列表成功（buyer_id: {buyer_id}, 数量：{len(transactions)}）")
            return transactions
    except Exception as e:
        logger.error(f"获取买家交易列表失败（buyer_id: {buyer_id}）：{str(e)}")
        return []
    finally:
        close_db_connection(conn)

def update_transaction_status(transaction_id: str, status: str) -> bool:
    """更新交易状态（如：待付款→待收货，待收货→交易完成）"""
    conn = get_db_connection()
    if not conn:
        return False
    
    try:
        with conn.cursor() as cursor:
            # 1. 先检查交易是否存在
            cursor.execute("SELECT * FROM transaction WHERE transaction_id = %s", (transaction_id,))
            if not cursor.fetchone():
                logger.warning(f"交易不存在（transaction_id: {transaction_id}）")
                return False
            
            # 2. 验证状态合法性（避免无效状态更新）
            valid_status = ["待付款", "待收货", "交易完成", "已取消"]
            if status not in valid_status:
                logger.error(f"无效交易状态（status: {status}）")
                return False
            
            # 3. 更新状态
            sql = "UPDATE transaction SET status = %s, update_time = CURRENT_TIMESTAMP WHERE transaction_id = %s"
            cursor.execute(sql, (status, transaction_id))
            conn.commit()
            logger.info(f"交易状态更新成功（transaction_id: {transaction_id}, 新状态：{status}）")
            return True
    except Exception as e:
        conn.rollback()
        logger.error(f"更新交易状态失败（transaction_id: {transaction_id}）：{str(e)}")
        return False
    finally:
        close_db_connection(conn)

def get_transaction_by_id(transaction_id: str) -> dict:
    """通过交易ID查询交易详情"""
    conn = get_db_connection()
    if not conn:
        return None
    
    try:
        with conn.cursor() as cursor:
            sql = "SELECT * FROM transaction WHERE transaction_id = %s"
            cursor.execute(sql, (transaction_id,))
            transaction = cursor.fetchone()
            if transaction:
                logger.info(f"获取交易详情成功（transaction_id: {transaction_id}）")
            else:
                logger.warning(f"交易不存在（transaction_id: {transaction_id}）")
            return transaction
    except Exception as e:
        logger.error(f"获取交易详情失败（transaction_id: {transaction_id}）：{str(e)}")
        return None
    finally:
        close_db_connection(conn)